package com.baoyouqun.mapper;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baoyouqun.entity.Demand;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;


@Mapper
public interface DemandMapper extends BaseMapper<Demand> {
    @Select("SELECT city_code AS cityCode, COUNT(*) AS count " +
            "FROM demand " +
            "WHERE city_code IS NOT NULL " +
            "GROUP BY city_code")
    List<Map<String, Object>> countByAreaCodes();

    /**
     * 根据帖子/评论ID查询所属用户ID
     *
     * @param targetIds 被举报对象ID列表
     * @return key:被举报对象ID, value:所属用户ID
     */
    @Select("<script>" +
            "SELECT id as target_id, user_id as owner_user_id " +
            "FROM demand " +
            "WHERE id IN " +
            "<foreach collection='targetIds' item='id' open='(' separator=',' close=')'>" +
            "#{id}" +
            "</foreach>" +
            "GROUP BY target_id, owner_user_id " + // 保证帖子唯一（target_id唯一），同时关联用户ID
            "</script>")
    List<Map<String, String>> selectOwnerMapByTargetIds(@Param("targetIds") List<String> targetIds);

    /*

     */
/**
 * 自定义SQL（三重引号长文本格式）：需求分页查询（筛选+动态排序+权重计算）
 *//*

    @Select("""
                <script>
                    SELECT 
                        id, user_id, type, city_name, city_code, tag, title, description,
                        file_list, phone_privacy, resource_urgency, never_expire, expire_time,
                        view_count, like_count, dislike_count, collect_count, status,
                        create_time, refresh_time,
                        -- 动态权重字段：仅推荐排序+有兴趣标签时生效
                        <if test='interestTags != null and interestTags.size() > 0 and (query.orderField == null or query.orderField == "recommend")'>
                            (CASE 
                                <foreach collection='interestTags' item='tag' index='index'>
                                    <if test='index == 0'>WHEN</if>
                                    <if test='index != 0'>OR</if>
                                    description LIKE CONCAT('%', #{tag}, '%') 
                                </foreach>
                            THEN 1 ELSE 0 END) AS weight,
                        </if>
                        -- 关联用户表：查询发布人名称（原transient字段publisherName）
                        (SELECT username FROM user WHERE id = demand.user_id) AS publisherName
                    FROM demand
                    WHERE status = 1  -- 固定筛选：仅正常状态（status=1）
                    -- 1. 关键词搜索：模糊匹配标题/描述
                    <if test='query.keyWord != null and query.keyWord != ""'>
                        AND (title LIKE CONCAT('%', #{query.keyWord}, '%') OR description LIKE CONCAT('%', #{query.keyWord}, '%'))
                    </if>
                    -- 2. 分类筛选：匹配标签及子标签（需传入预处理后的tagList）
                    <if test='query.tag != null and query.tag != ""'>
                        AND tag IN 
                        <foreach collection='query.tagList' item='tagItem' open='(' separator=',' close=')'>
                            #{tagItem}
                        </foreach>
                    </if>
                    -- 3. 地区筛选：匹配地区编码
                    <if test='query.cityCode != null and query.cityCode != ""'>
                        AND city_code = #{query.cityCode}
                    </if>
                    -- 4. 动态排序规则（对应原switch逻辑）
                    ORDER BY
                        <choose>
                            -- 推荐排序（默认/指定recommend）
                            <when test='query.orderField == null or query.orderField == "recommend"'>
                                <if test='interestTags != null and interestTags.size() > 0'>
                                    weight DESC,  -- 有兴趣标签：先按权重倒序
                                </if>
                                create_time DESC  -- 权重相同/无标签：按发布时间倒序
                            </when>
                            -- 最新排序（指定newest）
                            <when test='query.orderField == "newest"'>
                                create_time DESC
                            </when>
                            -- 点赞最多（指定like_most）
                            <when test='query.orderField == "like_most"'>
                                like_count DESC
                            </when>
                            -- 收藏最多（指定collect_most）
                            <when test='query.orderField == "collect_most"'>
                                collect_count DESC
                            </when>
                            -- 兜底排序：按发布时间
                            <otherwise>
                                create_time DESC
                            </otherwise>
                        </choose>
                </script>
            """)
    IPage<Demand> selectWxHomePage(
            IPage<Demand> page,  // 分页对象（MP自动处理limit/offset）
            @Param("query") DemandQuery query,  // 筛选条件（关键词/地区/分类等）
            @Param("interestTags") List<String> interestTags  // 用户兴趣标签（用于权重）
    );
*/


    /**
     * 批量统计用户帖子数（有效帖子：status=1）
     *
     * @param userIds 用户ID列表
     * @return 格式：[{user_id: "123", post_count: 5}, ...]
     */
    @Select("""
            <script>
                SELECT user_id, COUNT(id) AS post_count 
                FROM demand 
                WHERE user_id IN 
                <foreach collection="userIds" item="userId" open="(" separator="," close=")">
                    #{userId}
                </foreach>
                GROUP BY user_id
            </script>
            """)
    List<Map<String, Object>> selectBatchPostCount(List<String> userIds);


    /**
     * 关联用户表-分页查询
     *
     * @param page    分页参数（当前页、每页条数）
     * @param wrapper 筛选条件（关键词、分类、时间等）
     * @return 分页数据（含发布人名称publisherName）
     */
    @Select("SELECT d.*, u.`name` AS publisher_name " +
            "FROM demand d LEFT JOIN user u ON d.user_id = u.id " +
            "${ew.customSqlSegment}")
    IPage<Demand> pageWithUser(Page<Demand> page, @Param(Constants.WRAPPER) Wrapper<Demand> wrapper);

    /**
     * 关联用户表-全量查询（导出Excel专用）
     *
     * @param wrapper 筛选条件（与分页查询共用）
     * @return 全量数据（含发布人名称publisherName）
     */
    @Select("SELECT d.*, u.`name` AS publisher_name " +
            "FROM demand d LEFT JOIN user u ON d.user_id = u.id " +
            "${ew.customSqlSegment}")
    List<Demand> listWithUser(@Param(Constants.WRAPPER) Wrapper<Demand> wrapper);


    /**
     * 统计指定类型的需求总数（用于校验）
     */
    @Select("""
            <script>
            SELECT COUNT(id) 
            FROM demand 
            WHERE status = 1 AND type = #{demandType} 
            <if test='provinceCode != null and provinceCode != ""'>
                AND city_code IN (SELECT code FROM city WHERE parent_code = #{provinceCode}) 
            </if> 
            <if test='cityCode != null and cityCode != ""'>AND city_code = #{cityCode}</if>
            </script>
            """)
    Long selectDemandTotalCount(
            @Param("demandType") String demandType,
            @Param("provinceCode") String provinceCode,
            @Param("cityCode") String cityCode
    );

    /**
     * 按地区和类型统计需求数量
     * 区分发布需求和发布资源两种类型
     */
    @Select("SELECT " +
            "  city_code AS cityCode,  " +
            "  type,                   " +  // 需求类型字段
            "  COUNT(*) AS count       " +
            "FROM demand " +
            "WHERE city_code IS NOT NULL " +
            "GROUP BY city_code, type")
    // 按地区和类型双重分组
    List<Map<String, Object>> countByAreaAndType();
}